github.com/readium/readium-lcp-server@v0.0.0-20240509124024-799e77a0bbd6/dbmodel/postgres _db_setup_frontend.sql (about) 1 CREATE SEQUENCE publication_seq; 2 3 CREATE TABLE publication ( 4 id int PRIMARY KEY DEFAULT NEXTVAL ('publication_seq'), 5 uuid varchar(255) NOT NULL, /* == content id */ 6 title varchar(255) NOT NULL, 7 status varchar(255) NOT NULL 8 ); 9 10 CREATE INDEX uuid_index ON publication (uuid); 11 12 CREATE SEQUENCE user_seq; 13 14 CREATE TABLE "user" ( 15 id int PRIMARY KEY DEFAULT NEXTVAL ('user_seq'), 16 uuid varchar(255) NOT NULL, 17 name varchar(64) NOT NULL, 18 email varchar(64) NOT NULL, 19 password varchar(64) NOT NULL, 20 hint varchar(64) NOT NULL 21 ); 22 23 CREATE SEQUENCE purchase_seq; 24 25 CREATE TABLE purchase ( 26 id int PRIMARY KEY DEFAULT NEXTVAL ('purchase_seq'), 27 uuid varchar(255) NOT NULL, 28 publication_id int NOT NULL, 29 user_id int NOT NULL, 30 license_uuid varchar(255) NULL, 31 type varchar(32) NOT NULL, 32 transaction_date timestamp(0), 33 start_date timestamp(0), 34 end_date timestamp(0), 35 status varchar(255) NOT NULL, 36 FOREIGN KEY (publication_id) REFERENCES publication (id), 37 FOREIGN KEY (user_id) REFERENCES "user" (id) 38 ); 39 40 CREATE INDEX idx_purchase ON purchase (license_uuid); 41 42 CREATE SEQUENCE license_view_seq; 43 44 CREATE TABLE license_view ( 45 id int PRIMARY KEY DEFAULT NEXTVAL ('license_view_seq'), 46 uuid varchar(255) NOT NULL, 47 device_count int NOT NULL, 48 status varchar(255) NOT NULL, 49 message varchar(255) NOT NULL 50 );